 1.ClickHouse之SQL语法
   
   DDL
   建库：
   create databases mydatabase;
   执行完成以后，会在clickhouse的安装路径后生成mydatabase的文件目录：
   cd /var/lib/clickhouse/data
   ll
   在/var/lib/clickhouse/metadata路径下，会生成用于恢复数据库的.sql文件
   show databases查询数据库
   建表：三种方式
create table my_table ( Title String, URL String ,EventTime DateTime) ENGINE=Memory
create table if not exists new_db.hits_v1 as default.hits_v1 engine=TinyLog
create table if not exists hits_v1_1 engine=Memory as select * from hits_v1;
   查询表结构：
   desc
   删除表：
   drop table
   默认值表达式：
hdp-1 :) create table dfv_v1 (
:-] id String,
:-] c1 DEFAULT 1000,
:-] c2 String DEFAULT c1
:-] ) engine = TinyLog;
CREATE TABLE dfv_v1
(
`id` String,
`c1` DEFAULT 1000,
`c2` String DEFAULT c1
)
ENGINE = TinyLog
Ok.
0 rows in set. Elapsed: 0.018 sec.
hdp-1 :) insert into dfv_v1(id) values ('A000');

INSERT INTO dfv_v1 (id) VALUES
Ok.
1 rows in set. Elapsed: 0.009 sec.
hdp-1 :) select c1,c2,toTypeName(c1),toTypeName(c2) from dfv_v1;
SELECT
c1,
c2,
toTypeName(c1),
toTypeName(c2)
FROM dfv_v1
┌───c1─┬─c2───┬─toTypeName(c1)─┬─toTypeName(c2)─┐
│ 1000 │ 1000 │ UInt16 │ String │
└──────┴──────┴────────────────┴────────────────┘
1 rows in set. Elapsed: 0.002 sec.

   临时表：
   语法： 
create temporary table tmp_v1 (createtime Datetime);
   案例：
   如果临时表和正常表名字相同，临时表优先
   临时表的表引擎只能是Memory，数据是临时的，断电即无的数据。
   更多的是应用在clickhouse内部，是数据在集群间传播的载体 
hdp-1 :) create table tmp_v1( title String)engine = Memory;
CREATE TABLE tmp_v1
(
`title` String
)
ENGINE = Memory
Ok.
0 rows in set. Elapsed: 0.003 sec.

hdp-1 :) insert into tmp_v1 values ('click');
INSERT INTO tmp_v1 VALUES  
Ok.
1 rows in set. Elapsed: 0.001 sec.

hdp-1 :) create temporary table tmp_v1 (createtime Datetime);
CREATE TEMPORARY TABLE tmp_v1
(
`createtime` Datetime
)
Ok.
0 rows in set. Elapsed: 0.003 sec.

hdp-1 :) insert into tmp_v1 values (now());
INSERT INTO tmp_v1 VALUES
Ok.
1 rows in set. Elapsed: 0.010 sec.

hdp-1 :) select * from tmp_v1;
SELECT *
FROM tmp_v1
┌──────────createtime─┐
│ 2020-10-21 06:31:37 │
└─────────────────────┘ 

   分区表：
   只有合并数（MergeTree）家族的表引擎支持分区表
   可以利用分区表，做定位查询，缩小查询范围。
   分区字段不易设的太小
   案例：
CREATE TABLE partition_v1
(
`ID` String,
`URL` String,
`EventTime` Date
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID
insert into partition_v1 values ('A000','www.baidu.com','2020-05-01'),
('A001','www.sina.com','2020-06-01');
select table,partition,path from system.parts where table = 'partition_v1';
SELECT
table,
partition,
path
FROM system.parts
WHERE table = 'partition_v1'
┌─table────────┬─partition─┬─path────────────────────────────────────────────────────────┐
│ partition_v1 │ 202005 │ /var/lib/clickhouse/data/default/partition_v1/202005_1_1_0/ │
│ partition_v1 │ 202006 │ /var/lib/clickhouse/data/default/partition_v1/202006_2_2_0/ │
└──────────────┴───────────┴─────────────────────────────────────────────────────────────┘

   视图：
   普通视图和物化视图
   普通视图：不保存数据，只是一层单纯的select查询映射，起着简化查询、明晰语义的作用。
   物化视图：保存数据，如果源表被写入新数据，物化视图也会同步更新。
   POPULATE修饰符：决定在创建物化视图的过程中是否将源表的数据同步到物化视图里。
   
   数据表的基本操作：
   只有MergeTree、Merge、Distribution这三类表引擎支持alter操作。
   追加字段，两种方式：
   1 、alter table partition_v1 add column os String default 'mac';
   2 、alter table partition_v1 add column IP String after ID;

hdp-1 :) desc partition_v1;
DESCRIBE TABLE partition_v1
┌─name──────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_
expression─┐
│ ID │ String │ │ │ │ │
│
│ URL │ String │ │ │ │ │
│
│ EventTime │ Date │ │ │ │ │
│
└───────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴─────
───────────┘
3 rows in set. Elapsed: 0.001 sec.

hdp-1 :) alter table partition_v1 add column os String default 'mac';
ALTER TABLE partition_v1
ADD COLUMN `os` String DEFAULT 'mac'
Ok.
0 rows in set. Elapsed: 0.004 sec.

hdp-1 :) desc partition_v1;
DESCRIBE TABLE partition_v1
┌─name──────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_
expression─┐
│ ID │ String │ │ │ │ │
│
│ URL │ String │ │ │ │ │
│
│ EventTime │ Date │ │ │ │ │
│
│ os │ String │ DEFAULT │ 'mac' │ │ │
│
└───────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴─────
───────────┘

hdp-1 :) alter table partition_v1 add column IP String after ID;
ALTER TABLE partition_v1
ADD COLUMN `IP` String AFTER ID
Ok.
0 rows in set. Elapsed: 0.002 sec.

hdp-1 :) desc partition_v1;
DESCRIBE TABLE partition_v1
┌─name──────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_
expression─┐
│ ID │ String │ │ │ │ │
│
│ IP │ String │ │ │ │ │
│
│ URL │ String │ │ │ │ │
│
│ EventTime │ Date │ │ │ │ │
│
│ os │ String │ DEFAULT │ 'mac' │ │ │
│
└───────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴─────
───────────┘
5 rows in set. Elapsed: 0.001 sec.

   修改数据类型：
   alter...modify column...
   注意：类型需要相互兼容
hdp-1 :) alter table partition_v1 modify column IP IPv4;
ALTER TABLE partition_v1
MODIFY COLUMN `IP` IPv4
Ok.
0 rows in set. Elapsed: 0.012 sec.
hdp-1 :) desc partition_v1;
DESCRIBE TABLE partition_v1
┌─name──────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_
expression─┐
│ ID │ String │ │ │ │ │
│
│ IP │ IPv4 │ │ │ │ │
│
│ URL │ String │ │ │ │ │
│
│ EventTime │ Date │ │ │ │ │
│
│ os │ String │ DEFAULT │ 'mac' │ │ │
│
└───────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴─────
───────────┘

   修改备注：
   alter...comment column ...
ALTER TABLE partition_v1
COMMENT COLUMN ID '主键ID'
Ok.
0 rows in set. Elapsed: 0.006 sec.
hdp-1 :) desc partition_v1;
DESCRIBE TABLE partition_v1
┌─name──────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_
expression─┐
│ ID │ String │ │ │ 主键ID │ │
│
│ IP │ IPv4 │ │ │ │ │
│
│ URL │ String │ │ │ │ │
│
│ EventTime │ Date │ │ │ │ │
│
│ os │ String │ DEFAULT │ 'mac' │ │ │
│
└───────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴─────
───────────┘
5 rows in set. Elapsed: 0.001 sec.

   删除已有字段：
   会把该字段下的数据一起删除
alter table partition_v1 drop column URL
   移动数据表
   rename... to...
   注意：表的移动只能在单节点内完成
rename table default.partition_v1 to system.partition_v1;  
   
   数据分区的基本操作：
   查询分区信息：
   ClickHouse内置了很多system系统表，用于查询自身状态信息。   
USE system
Ok.
0 rows in set. Elapsed: 0.001 sec.

hdp-1 :) show tables;
SHOW TABLES
┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ asynchronous_metric_log │
│ asynchronous_metrics │
│ build_options │
│ clusters │
│ collations │
│ columns │
│ contributors │
│ current_roles │
│ data_type_families │
│ databases │
│ detached_parts │
│ dictionaries │
│ disks │
│ distribution_queue │
│ enabled_roles │
│ events │
│ formats │
│ functions │
│ grants │
│ graphite_retentions │
│ licenses │
│ macros │
│ merge_tree_settings │
│ merges │
│ metric_log │
│ metrics │
│ models │
│ mutations │
│ numbers │
│ numbers_mt │
│ one │
│ parts │
│ parts_columns │
│ privileges │
│ processes │
│ query_log │
│ query_thread_log │
│ quota_limits │
│ quota_usage │
│ quotas │
│ quotas_usage │
│ replicas │
│ replication_queue │
│ role_grants │
│ roles │
│ row_policies │
│ settings │
│ settings_profile_elements │
│ settings_profiles │
│ stack_trace │
│ storage_policies │
│ table_engines │
│ table_functions │
│ tables │
│ trace_log │
│ users │
│ zeros │
│ zeros_mt │
│ zookeeper │
└────────────────────────────────┘
60 rows in set. Elapsed: 0.002 sec.

   其中parts是专门用来查询分区信息的表
   desc parts
┌─name──────────────────────────────────┬─type────────────┬─default_type─┬─default_expressio
n
│ partition │ String │ │
│ name │ String │ │
│ part_type │ String │ │
│ active │ UInt8 │ │
│ marks │ UInt64 │ │
│ rows │ UInt64 │ │
│ bytes_on_disk │ UInt64 │ │
│ data_compressed_bytes │ UInt64 │ │
│ data_uncompressed_bytes │ UInt64 │ │
│ marks_bytes │ UInt64 │ │
│ modification_time │ DateTime │ │
│ remove_time │ DateTime │ │
│ refcount │ UInt32 │ │
│ min_date │ Date │ │
│ max_date │ Date │ │
│ min_time │ DateTime │ │
│ max_time │ DateTime │ │
│ partition_id │ String │ │
│ min_block_number │ Int64 │ │
│ max_block_number │ Int64 │ │
│ level │ UInt32 │ │
│ data_version │ UInt64 │ │
│ primary_key_bytes_in_memory │ UInt64 │ │
│ primary_key_bytes_in_memory_allocated │ UInt64 │ │
│ is_frozen │ UInt8 │ │
│ database │ String │ │
│ table │ String │ │
│ engine │ String │ │
│ disk_name │ String │ │
│ path │ String │ │
│ hash_of_all_files │ String │ │
│ hash_of_uncompressed_files │ String │ │
│ uncompressed_hash_of_compressed_files │ String │ │
│ delete_ttl_info_min │ DateTime │ │
│ delete_ttl_info_max │ DateTime │ │
│ move_ttl_info.expression │ Array(String) │ │
│ move_ttl_info.min │ Array(DateTime) │ │
│ move_ttl_info.max │ Array(DateTime) │ │
│ bytes │ UInt64 │ ALIAS │ bytes_on_disk
│ marks_size │ UInt64 │ ALIAS │ marks_bytes
└───────────────────────────────────────┴─────────────────┴──────────────┴──────────────────

hdp-1 :) select partition_id,name,table,database from system.parts where table =
'partition_v1';
SELECT
partition_id,
name,
table,
database
FROM system.parts
WHERE table = 'partition_v1'
┌─partition_id─┬─name───────────┬─table────────┬─database─┐
│ 202005 │ 202005_1_1_0_3 │ partition_v1 │ default │
│ 202006 │ 202006_2_2_0_3 │ partition_v1 │ default │
└──────────────┴────────────────┴──────────────┴──────────┘
2 rows in set. Elapsed: 0.006 sec.

   删除指定分区：
alter table partition_v1 drop partition 202005
   可以利用删除完成更新操作---先删除，再insert插入
   复制分区数据
   需要满足两个条件
   1 、两张表需要有相同的分区键
   2 、两张表需要有相同的表结构
alter table partition_v2 replace partition 202005 from partition_v1;
   重置分区数据
alter table partition_v1 clear clumn URL in partition 202005
   卸载和装载分区
   卸载：alter table ...detach..
   装载： alter table ...attach...
alter table partition_v1 detach partition 202005
alter table partition_v1 attach partition 202005
   分布式DDL执行
   在集群上的任意一个节点上执行DDL语句，那么集群上的任意一个节点都会以相同的顺序执行相同的语义。
   
   数据写入：
   方式1：
   insert into partition_v1 values (...)
   方式2：指定格式 format CSV
[root@hdp-1 input]# cat mycsv.csv
A003,0.0.0.0,www.aa.com,2020-05-05,mac
A004,0.0.0.0,www.cc.com,2020-05-06,window
[root@hdp-1 input]# cat mycsv.csv | clickhouse-client --query="insert into
default.partition_v1 format CSV"

hdp-1 :) select * from partition_v1;
SELECT *
FROM partition_v1
┌─ID───┬──────IP─┬─URL────────┬──EventTime─┬─os─────┐
│ A003 │ 0.0.0.0 │ www.aa.com │ 2020-05-05 │ mac │
│ A004 │ 0.0.0.0 │ www.cc.com │ 2020-05-06 │ window │
└──────┴─────────┴────────────┴────────────┴────────┘
┌─ID───┬──────IP─┬─URL───────────┬──EventTime─┬─os──┐
│ A000 │ 0.0.0.0 │ www.baidu.com │ 2020-05-01 │ mac │
└──────┴─────────┴───────────────┴────────────┴─────┘
┌─ID───┬──────IP─┬─URL──────────┬──EventTime─┬─os──┐
│ A001 │ 0.0.0.0 │ www.sina.com │ 2020-06-01 │ mac │
└──────┴─────────┴──────────────┴────────────┴─────┘
┌─ID───┬──────IP─┬─URL─────────────┬──EventTime─┬─os─────┐
│ A002 │ 0.0.0.0 │ www.youtube.com │ 2020-05-03 │ window │
└──────┴─────────┴─────────────────┴────────────┴────────┘
5 rows in set. Elapsed: 0.004 sec.

   创建partition_v2
hdp-1 :) create table partition_v2 (ID String, IP IPv4, URL String,EventTime Date, os
String) engine=MergeTree partition by toYYYYMM(EventTime) order by ID;
CREATE TABLE partition_v2
(
`ID` String,
`IP` IPv4,
`URL` String,
`EventTime` Date,
`os` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID

   方式3：select方式
hdp-1 :) insert into partition_v2 select * from partition_v1;
INSERT INTO partition_v2 SELECT *
FROM partition_v1
Ok.
0 rows in set. Elapsed: 0.006 sec.
hdp-1 :) select * from partition_v2;
SELECT *
FROM partition_v2
┌─ID───┬──────IP─┬─URL──────────┬──EventTime─┬─os──┐
│ A001 │ 0.0.0.0 │ www.sina.com │ 2020-06-01 │ mac │
└──────┴─────────┴──────────────┴────────────┴─────┘
┌─ID───┬──────IP─┬─URL─────────────┬──EventTime─┬─os─────┐
│ A000 │ 0.0.0.0 │ www.baidu.com │ 2020-05-01 │ mac │
│ A002 │ 0.0.0.0 │ www.youtube.com │ 2020-05-03 │ window │
│ A003 │ 0.0.0.0 │ www.aa.com │ 2020-05-05 │ mac │
│ A004 │ 0.0.0.0 │ www.cc.com │ 2020-05-06 │ window │
└──────┴─────────┴─────────────────┴────────────┴────────┘

   1).CREATE
   (1).CREATE DATABASE
   用于创建指定名称的数据库，语法如下：
   CREATE DATABASE [IF NOT EXISTS] db_name
   如果查询中存在IF NOT EXISTS，则当数据库已经存在时，该查询不会返回任何错误。
:) create database test;
Ok.
0 rows in set. Elapsed: 0.018 sec.
   (2).CREATE TABLE
   对于创建表，语法如下：
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = engine
   DEFAULT expr – 默认值，用法与SQL类似。
   MATERIALIZED expr – 物化表达式，被该表达式指定的列不能被INSERT，因为它总是被计算出来的。 对于INSERT而
言，不需要考虑这些列。 另外，在SELECT查询中如果包含星号，此列不会被查询。
   ALIAS expr – 别名。
   有三种方式创建表：
   1)直接创建
:) create table t1(id UInt16,name String) engine=TinyLog
   2)创建一个与其他表具有相同结构的表
   CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]
   可以对其指定不同的表引擎声明。如果没有表引擎声明，则创建的表将与db2.name2使用相同的表引擎。
:) create table t2 as t1 engine=Memory
:) desc t2
DESCRIBE TABLE t2
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ id │ UInt16 │ │ │
│ name │ String │ │ │
└──────┴────────┴──────────────┴────────────────────┘
   3)使用指定的引擎创建一个与SELECT子句的结果具有相同结构的表，并使用SELECT子句的结果填充它。
   语法：
   CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...
   实例：
   先在t2中插入几条数据
:) insert into t1 values(1,'zhangsan'),(2,'lisi'),(3,'wangwu')
:) create table t3 engine=TinyLog as select * from t1
:) select * from t3
┌─id─┬─name─────┐
│ 1 │ zhangsan │
│ 2 │ lisi │
│ 3 │ wangwu │
└────┴──────────┘
   2).INSERT INTO
   主要用于向表中添加数据，基本格式如下：
   INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
   实例：
:) insert into t1 values(1,'zhangsan'),(2,'lisi'),(3,'wangwu')
   还可以使用select来写入数据：
   INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...
   实例：
:) insert into t2 select * from t3
:) select * from t2
┌─id─┬─name─────┐
│ 1 │ zhangsan │
│ 2 │ lisi │
│ 3 │ wangwu │
└────┴──────────┘
   ClickHouse不支持的修改数据的查询：UPDATE, DELETE, REPLACE, MERGE, UPSERT, INSERT UPDATE。
   3).ALTER
   ALTER只支持MergeTree系列，Merge和Distributed引擎的表，基本语法：
   ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|MODIFY COLUMN ...
   参数解析：
   ADD COLUMN – 向表中添加新列
   DROP COLUMN – 在表中删除列
   MODIFY COLUMN – 更改列的类型
   案例演示：
   1)创建一个MergerTree引擎的表
create table mt_table (date Date, id UInt8, name String) ENGINE=MergeTree(date, (id, name), 8192);
   2)向表中插入一些值
insert into mt_table values ('2019-05-01', 1, 'zhangsan');
insert into mt_table values ('2019-06-01', 2, 'lisi');
insert into mt_table values ('2019-05-03', 3, 'wangwu');
   3)在末尾添加一个新列age
:)alter table mt_table add column age UInt8
:)desc mt_table
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date │ │ │
│ id │ UInt8 │ │ │
│ name │ String │ │ │
│ age │ UInt8 │ │ │
└──────┴────────┴──────────────┴────────────────────┘
:) select * from mt_table
┌───────date─┬─id─┬─name─┬─age─┐
│ 2019-06-01 │ 2 │ lisi │ 0 │
└────────────┴────┴──────┴─────┘
┌───────date─┬─id─┬─name─────┬─age─┐
│ 2019-05-01 │ 1 │ zhangsan │ 0 │
│ 2019-05-03 │ 3 │ wangwu │ 0 │
└────────────┴────┴──────────┴─────┘
   4)更改age列的类型
:)alter table mt_table modify column age UInt16
:)desc mt_table
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date │ │ │
│ id │ UInt8 │ │ │
│ name │ String │ │ │
│ age │ UInt16 │ │ │
└──────┴────────┴──────────────┴────────────────────┘
   5)删除刚才创建的age列
:)alter table mt_table drop column age
:)desc mt_table
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date │ │ │
│ id │ UInt8 │ │ │
│ name │ String │ │ │
└──────┴────────┴──────────────┴────────────────────┘
   4).DESCRIBE TABLE
   查看表结构
:)desc mt_table
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date │ │ │
│ id │ UInt8 │ │ │
│ name │ String │ │ │
└──────┴────────┴──────────────┴────────────────────┘
   5).CHECK TABLE
   检查表中的数据是否损坏，他会返回两种结果：
   0 – 数据已损坏
   1 – 数据完整
   该命令只支持Log，TinyLog和StripeLog引擎。
  